/***************************************************************************************************
Table 1: Summary statistics on transactions (net purchases in thousands of dollars)
***************************************************************************************************/
foreach yyyy of numlist 1994/2019{
	import delimited "$MyPath/Data/Micro Data/sum_stats_transactions_`yyyy'", clear
	gen year = `yyyy'
	tempfile temp`yyyy'
	save `temp`yyyy''
}
drop _all
foreach yyyy of numlist 1994/2019{
	append using `temp`yyyy''
}
rename v1 variable
collapse (mean) mean sd p1 p10 p25 p50 p75 p90 p99, by(variable)
gen order = 1 if regexm(variable, "housing")
gen label = "Housing" if regexm(variable, "housing")
replace order = 2 if regexm(variable, "debt")
replace label = "Debt" if regexm(variable, "debt")
replace order = 3 if regexm(variable, "deposits")
replace label = "Deposits" if regexm(variable, "deposits")
replace order = 4 if regexm(variable, "equity")
replace label = "Equity" if regexm(variable, "equity")
sort order
format mean sd p1 p10 p25 p50 p75 p90 p99 %3.1fc


* invert sign and percentile for debt
sum p99 if variable == "debt_total"
local m = r(mean)
replace p99 = -p1 if variable == "debt_total"
replace p1 = -`m' if variable == "debt_total"
sum p90 if variable == "debt_total"
local m = r(mean)
replace p90 = -p10 if variable == "debt_total"
replace p10 = -`m' if variable == "debt_total"
sum p75 if variable == "debt_total"
local m = r(mean)
replace p75 = -p25 if variable == "debt_total"
replace p25 = -`m' if variable == "debt_total"
replace p50 = - p50 if variable == "debt_total"
replace mean = - mean if variable == "debt_total"

listtab label mean sd p1 p10 p25 p50 p75 p90 p99  using  "$MyPath/tables/Table1.tex", rstyle(tabular)  replace

/***************************************************************************************************
Appendix Table A2: Individual wealth at the end of 1993
***************************************************************************************************/
import delimited "$MyPath/Data/Micro Data/sum_stats_stocks_1993", clear
rename v1 variable
gen order = 1 if variable == "total_wealth"
gen label = "Total wealth" if variable == "total_wealth"
replace order = 2 if variable == "financial_wealth"
replace label = "\noalign{\vskip 1.5mm} Financial wealth" if variable == "financial_wealth"
replace order = 3 if variable == "housing"
replace label = "\enspace Housing" if variable == "housing"
replace order = 4 if variable == "debt"
replace label = "\enspace Debt" if variable == "debt"
replace order = 5 if variable == "deposits"
replace label = "\enspace Deposits" if variable == "deposits"
replace order = 6 if variable == "public_equity"
replace label = "\enspace Public equity" if variable == "public_equity"
replace order = 7 if variable == "private_equity"
replace label = "\enspace Private equity" if variable == "private_equity"
replace order = 8 if variable == "human_wealth"
replace label = "\noalign{\vskip 1.5mm} Human wealth" if variable == "human_wealth"
sort order
format mean sd p1 p10 p25 p50 p75 p90 p99 %3.1fc

* invert sign and percentile for debt
sum p99 if variable == "debt"
local m = r(mean)
replace p99 = -p1 if variable == "debt"
replace p1 = -`m' if variable == "debt"
sum p90 if variable == "debt"
local m = r(mean)
replace p90 = -p10 if variable == "debt"
replace p10 = -`m' if variable == "debt"
sum p75 if variable == "debt"
local m = r(mean)
replace p75 = -p25 if variable == "debt"
replace p25 = -`m' if variable == "debt"
replace p50 = -p50 if variable == "debt"
replace mean = - mean if variable == "debt"
listtab label mean sd p10 p25 p50 p75 p90 p99  using  "$MyPath/tables/TableA2.tex", rstyle(tabular)  replace


* Output number used in Footnote of Appendix Table A2 
tempname handle
file open `handle' using "$MyPath/Numbers/wealth_nobs.tex", write replace
sum count
file write `handle' "`:di %9.0fc `=r(mean)''"
file close `handle'

/***************************************************************************************************
Output numbers used in Introduction and in Section 3.1 Redistribution across individuals
***************************************************************************************************/
import delimited "$MyPath/Data/Micro Data/sum_stats_welfare_1994", clear
rename v1 variable
keep if variable == "welfare_total"


tempname handle
file open `handle' using "$MyPath/Numbers/wf_p1.tex", write replace
file write `handle' "-\\$`: di  %9.0fc abs(p1[1])',000"
file close `handle'

tempname handle
file open `handle' using "$MyPath/Numbers/wf_p99.tex", write replace
file write `handle' "\\$`: di  %9.0fc p99[1]',000"
file close `handle'

tempname handle
file open `handle' using "$MyPath/Numbers/wf_iqr.tex", write replace
file write `handle' "\\$`: di  %9.0fc p75[1] - p25[1]',000"
file close `handle'


tempname handle
file open `handle' using "$MyPath/Numbers/wf_sk_kelly.tex", write replace
file write `handle' "`: di  %9.2fc (p90[1] + p10[1] - 2 * p50[1]) / (p90[1] - p10[1])'"
file close `handle'

/***************************************************************************************************
Output numbers in Section 3.1 Redistribution across individuals
***************************************************************************************************/
import delimited using "$MyPath/Data/Micro Data/hist_welfare_total_sc_1994", clear
sum  x [w = y], d

local m = r(mean)
* %9.1fc will look like zero
if abs(`m') < 0.04{
	local m = abs(`m')
}
tempname handle
file open `handle' using "$MyPath/Numbers/wf_sc_mean.tex", write replace
file write `handle' "`: di  %9.1fc abs(`m')'"
file close `handle'

tempname handle
file open `handle' using "$MyPath/Numbers/wf_sc_p1.tex", write replace
file write `handle' "`: di  %9.0fc r(p1)'"
file close `handle'

tempname handle
file open `handle' using "$MyPath/Numbers/wf_sc_p99.tex", write replace
file write `handle' "`: di  %9.0fc r(p99)'"
file close `handle'

tempname handle
file open `handle' using "$MyPath/Numbers/wf_sc_iqr.tex", write replace
file write `handle' "`: di  %9.1fc r(p75) - r(p25)'"
file close `handle'


tempname handle
file open `handle' using "$MyPath/Numbers/wf_sc_sk_kelly.tex", write replace
file write `handle' "`: di  %9.2fc (r(p90) + r(p10) - 2 * r(p50)) / (r(p90) - r(p10))'"
file close `handle'

tempname handle
file open `handle' using "$MyPath/Numbers/wf_sc_kurt.tex", write replace
file write `handle' "`: di  %9.0fc (r(kurtosis))'"
file close `handle'

/***************************************************************************************************
Table 2: Decomposition of welfare gains by percentile groups
***************************************************************************************************/
import delimited "$MyPath/Data/Micro Data/welfare_effect_decompose_100", clear
gen group = .
replace percentile = percentile + 1
replace group = 1 if inrange(percentile, 1, 1)
replace group = 2 if inrange(percentile, 2, 10)
replace group = 3 if inrange(percentile, 11, 50)
replace group = 4 if inrange(percentile, 51, 89)
replace group = 5 if inrange(percentile, 90, 99)
replace group = 6 if inrange(percentile, 99, 100)
preserve
collapse (mean) welfare*, by(group)
tempfile temp
save `temp'
restore
collapse (mean) welfare*
gen group = 0
append using `temp'

gather welfare*
spread group value
replace variable = subinstr(variable, "welfare_", "", .)
replace variable = subinstr(variable, "_mean", "", .)

gen label = ""

gen order = .
replace label = "Housing" if variable == "housing"
replace order = 1 if variable == "housing"
replace label = "Debt" if variable == "debt"
replace order = 2 if variable == "debt"
replace label = "Deposits" if variable == "deposits"
replace order = 3 if variable == "deposits"
replace label = "Equity" if variable == "equity"
replace order = 4 if variable == "equity"
replace label = "\noalign{\vskip 1.5mm} Total" if variable == "total"
replace order = 5 if variable == "total"
drop if missing(order)
sort order

format  value*  %3.1fc
listtab label value*  using  "$MyPath/tables/Table2.tex", rstyle(tabular)  replace

tempname handle
file open `handle' using "$MyPath/Numbers/wf_mean.tex", write replace
sum value0 if variable == "total" 
file write `handle' "\\$`: di  %9.0fc r(mean)',000"
file close `handle'


tempname handle
file open `handle' using "$MyPath/Numbers/wf_p99_bin.tex", write replace
sum value6 if variable == "total" 
file write `handle' "\\$`: di  %9.0fc r(mean)',000"
file close `handle'

/***************************************************************************************************
Figure 4a: Distribution of welfare gains ; Welfare gains in dollar terms
***************************************************************************************************/
import delimited using "$MyPath/Data/Micro Data/hist_welfare_total_1994", clear
egen t = sum(y * inrange(x, -150, 150))
twoway (bar y x if inrange(x, -150, 150), barwidth(4)  color("black") fcolor("black%0")) ///
, ysize(4) xsize(6) graphregion(color(white) margin(l=0))  ///
xlabel(-150 "-150K" -75 "-75K" 0 "0K" 75 "75K" 150 "150K", labsize(medlarge) grid glcolor(gs14) glwidth(vthin)) ///
ylabel(0 "0" 0.05 "0.05" 0.1 "0.10" 0.15 "0.15" 0.2 "0.20" 0.25 "0.25", labsize(medlarge) grid glcolor(gs14) glwidth(vthin) angle(0)) ///
xtitle("", size(medlarge)) ///
ytitle("", size(medlarge)) 
graph export "$MyPath/Figures/Figure4a.$suffix", replace

/***************************************************************************************************
Figure 4b: Distribution of welfare gains ; Welfare gains as percent of total wealth
***************************************************************************************************/
import delimited using "$MyPath/Data/Micro Data/hist_welfare_total_sc_1994", clear
twoway (bar y x if inrange(x, -30, 30), barwidth(0.8)  color("black") fcolor("black%0")) ///
, ysize(4) xsize(6) graphregion(color(white) margin(l=0))  ///
xlabel(-30 "-30%" -15 "-15%" 0 "0%" 15 "15%" 30 "30%", labsize(medlarge) grid glcolor(gs14) glwidth(vthin) angle(0)) ///
ylabel(0 "0" 0.05 "0.05" 0.1 "0.10" 0.15 "0.15" 0.2 "0.20" 0.25 "0.25", labsize(medlarge) grid glcolor(gs14) glwidth(vthin) angle(0)) /// 
yscale(r(0 0.27)) ///
xtitle("", size(medlarge)) ///
ytitle("", size(medlarge))
graph export "$MyPath/Figures/Figure4b.$suffix", replace

/**************************************************************************************************
Figure 5: Social welfare gain as a function of inequality aversion
***************************************************************************************************/
import delimited "$MyPath/Data/Micro Data/planner_welfare", clear
rename a gamma
rename b welfare

twoway (line welfare gamma if inrange(gamma, 0, 2.6), lwidth(thick) color(black)) ///
, yline(0, lpattern(solid) lwidth(thin) lcolor(black)) /// 
ysize(4) xsize(6) graphregion(color(white) margin(l=0)) /// 
xlabel(0[1]2, labsize(medlarge) grid glcolor(gs14) glwidth(vthin)) ///
ylabel(-5 "-5K" 0 "0K" 5 "5K" 10 "10K" , labsize(medlarge) grid glcolor(gs14) glwidth(vthin) angle(0)) ///
ytitle("Social welfare gain", size(medlarge)) ///
xtitle("Social planner aversion to inequality {&sigma}", size(medlarge)) ///
legend(off)
graph export "$MyPath/Figures/Figure5.$suffix", replace

/***************************************************************************************************
Figure 6a: Welfare gains versus revaluation gains as percent of total wealth
***************************************************************************************************/
import delimited using "$MyPath/Data/Micro Data/hist_welfare_total_sc_1994", clear
gen variable = "welfare"
tempfile temp
save `temp'
import delimited using "$MyPath/Data/Micro Data/hist_revgain_total_sc_1994", clear
gen variable = "revaluation"
append using `temp'

twoway (bar y x if inrange(x, -30, 60) & variable == "welfare", barwidth(0.8) fcolor("black%0") lcolor("black")) ///
(bar y x if inrange(x, -30, 60) & variable == "revaluation", barwidth(0.8) fcolor("black%30") lcolor("black%0")) ///
, ysize(4) xsize(6) graphregion(color(white) margin(l=0))  ///
xlabel(-30 "-30%" -15 "-15%" 0 "0%" 15 "15%" 30 "30%" 45 "45%" 60 "60%", labsize(medlarge) grid glcolor(gs14) glwidth(vthin)) ///
ylabel(0 "0" 0.05 "0.05" 0.1 "0.10" 0.15 "0.15" 0.2 "0.20" 0.25 "0.25", labsize(medlarge) grid glcolor(gs14) glwidth(vthin) angle(0))  ///
legend(order(1 "Welfare Gains" 2 "Revaluation Gains")) ///
ytitle("", size(medlarge)) ///
xtitle("", size(medlarge)) 
graph export "$MyPath/Figures/Figure6a.$suffix", replace

* Number used in intro and  in Section 3.1 Redistribution across individuals
sum x [w = y] if variable == "revaluation"
tempname handle
file open `handle' using "$MyPath/Numbers/wg_sc_mean.tex", write replace
file write `handle' "`: di  %3.1fc r(mean)'"
file close `handle'

/***************************************************************************************************
Figure 6b: Welfare gains versus revaluation gains as percent of total wealth
***************************************************************************************************/
import delimited using "$MyPath/Data/Micro Data/welfare_revgain_sc_heatmap_5_1994", clear
fillin revgain_rank_5 welfare_rank_5
gen label = fraction * 100
tostring label, format(%9.1fc) replace force
replace label = label + "%"
heatplot fraction  revgain_rank_5 welfare_rank_5, values(label(label) size(medium)) ///
ysize(4) xsize(6) graphregion(color(white) margin(l=0))  ///
xlabel(1 2 3 4 5, labsize(medlarge) nogrid) ///
ylabel(1 2 3 4 5, labsize(medlarge) nogrid angle(0)) ///
xtitle("Quintile of welfare gains", size(medlarge)) ///
ytitle("Quintile of revaluation gains", size(medlarge)) ///
legend(off) ///
colors("255 245 240*0.8" "254 224 210*0.8" "252 187 161*0.8" "252 146 114" "251 106 74*0.8" "239 59 44*0.8")
graph export "$MyPath/Figures/Figure6b.$suffix", replace

/***************************************************************************************************
Correlation Scaled Welfare and Revaluation gains
***************************************************************************************************/
import delimited using "$MyPath/Data/Micro Data/corr_welfare_revgain_sc_rank_1994", clear

tempname handle
file open `handle' using "$MyPath/Numbers/rank_corr_sc.tex", write replace
file write `handle' "`: di  %3.2fc v1[1]'"
file close `handle'

/***************************************************************************************************
Appendix Table A11: Distribution of the effect of borrowing constraints on welfare gains
***************************************************************************************************/
import delimited "$MyPath/Data/Micro Data/sum_stats_welfare_total_diff_xi_10_1994", clear
gen label = "\$\xi = 0.01\$"
tempfile temp0
save `temp0'

import delimited "$MyPath/Data/Micro Data/sum_stats_welfare_total_diff_xi_5_1994", clear
gen label = "\$\xi = 0.005\$"
append using `temp0'
format mean p*  %3.1fc
listtab label mean p*   using  "$MyPath/tables/TableA11.tex", rstyle(tabular)  replace

tempname handle
file open `handle' using "$MyPath/Numbers/wf_collateral_p99_bin.tex", write replace
sum p99100 if label == "\$\xi = 0.01\$"
file write `handle' "\\$`: di  %9.0fc r(mean)',000"
file close `handle'

/***************************************************************************************************
Appendix Figure A10: Distribution of the effect of borrowing constraints on welfare gains
***************************************************************************************************/
import delimited using "$MyPath/Data/Micro Data/hist_welfare_total_diff_xi_10_1994", clear
replace x = round(x * 5) / 5
collapse (sum) y, by(x)
twoway (bar y x if inrange(x, -20, 20) & x != 0, barwidth(0.2)  color("black") fcolor("black%0")) ///
, ysize(4) xsize(6) graphregion(color(white) margin(l=0))  ///
xlabel(-20 "-20K"  -10 "-10K"  0 "0K" 10 "10K"  20 "20K" , labsize(medlarge) grid glcolor(gs14) glwidth(vthin)) ///
ylabel(, labsize(medlarge) grid glcolor(gs14) glwidth(vthin) angle(0)) ///
xtitle("Effect of borrowing constraints on welfare gains", size(medlarge)) ///
ytitle("", size(medlarge))
graph export "$MyPath/Figures/FigureA10.$suffix", replace

/***************************************************************************************************
Figure 11a: Accounting for borrowing constraints and collateral effects ; Interest rates and loan-to-value ratios
***************************************************************************************************/
import delimited "$MyPath/Data/Micro Data/interest_rate_and_ltv", clear 
scatter interest_rate_demeaned l_ltv_a, color(black) msize(small) /// 
ysize(4) xsize(6) graphregion(color(white) margin(l=0)) ///
xlabel(0.2 "0.2" 0.4 "0.4" 0.6 "0.6" 0.8 "0.8" 1 "1", labsize(medlarge) grid glcolor(gs14) glwidth(vthin)) ///
ylabel(4.95 "4.95%" 5 "5.00%" 5.05 "5.05%" 5.10 "5.10%" 5.15 "5.15%" 5.20 "5.20%" , labsize(medlarge) grid glcolor(gs14) glwidth(vthin) angle(0)) ///
yscale(range(4.95 5.20)) ///
xtitle("Ratio of mortgage debt to house value", size(medlarge)) ytitle("", size(medlarge)) ///
legend(on order(1 "Mortage interest rate") size(medlarge) symysize(6) symxsize(8) region(lwidth(vvthin))) 
graph export "$MyPath/Figures/Figure11a.$suffix", replace

/***************************************************************************************************
Average loan to value including business debt (Number used in Section 4.2 Borrowing constraints and collateral effects)
***************************************************************************************************/
import delimited "$MyPath/Data/Micro Data/year_mean_ltv", clear 
sum ltvmean
tempname handle
file open `handle' using "$MyPath/Numbers/ltv.tex", write replace
file write `handle' "`:di %3.2fc `=r(mean)''"
file close `handle'

/***************************************************************************************************
Table 
***************************************************************************************************/
import delimited "$MyPath/Data/Micro Data/interest_rate_and_ltv_regression", clear 
keep if _n >= 3
destring v2 v3, replace force
tempname handle
file open `handle' using "$MyPath/Tables/TableA9.tex", write replace
file write `handle' " Loan-to-value  & `:di %5.4fc `=v2[1]'' & `:di %5.4fc `=v3[1]''  \\" _n
file write `handle' "\vspace{4pt} & (`:di %6.5fc `=v2[2]'')  & (`:di %6.5fc `=v3[2]'') &   \\ " _n
file write `handle' "\noalign{\vskip 2mm}" _n
file write `handle' "Year fixed effects  & $\checkmark$ & $\checkmark$ \\" _n
file write `handle' "Age and education controls  & & $\checkmark$ \\" _n
file write `handle' "\noalign{\vskip 2mm}" _n
file write `handle' "Sample size & `:di %12.0fc `=v2[3]''  & `:di %12.0fc `=v3[3]'' \\" _n
file write `handle' "\$R^2\$ & (`:di %4.3fc `=v2[4]'') & (`:di %4.3fc `=v3[4]'')   \\" _n
file close `handle'
